﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for _User_company
/// </summary>
public class _User_company
{
    static SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);

    public _User_company()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public int _id_user_company { get; set; }
    public int _id_company { get; set; }
    public string _company_name { get; set; }
    public string _company_reg_nr { get; set; }
    public string _company_vat { get; set; }
    public string _status { get; set; }
    public int _id_user { get; set; }
    public bool _main_company { get; set; }
    public string _position { get; set; }
    public string _client { get; set; }

    private static _User_company Fill_User_company(SqlDataReader dr)
    {
        _User_company user_company = new _User_company();

        user_company._id_user_company = (int)dr[0];

        user_company._id_company = (int)dr[1];
        user_company._company_name = _Company.SelectCompany(dr[1].ToString(),"")._company_name;
        user_company._company_reg_nr = _Company.SelectCompany(dr[1].ToString(),"")._company_reg_nr;
        user_company._company_vat = _Company.SelectCompany(dr[1].ToString(),"")._company_vat;
        user_company._status = _Company.SelectCompany(dr[1].ToString(),"")._status;

        user_company._id_user = (int)dr[2];
        user_company._main_company = (bool)dr[3];
        user_company._position = dr[4].ToString();
        user_company._client = dr[5].ToString();

        return user_company;
    }

    //Atlasam nepieciešamo priekš MyCompanies.aspx saraksta
    public static List<_User_company> Select_User_company(string user_id)
    {
        List<_User_company> list = new List<_User_company>();
        SqlCommand sCmd = new SqlCommand("SELECT * FROM user_company WHERE id_user=@user_id AND client=@clientt", conn);
        sCmd.Parameters.Add(new SqlParameter("@user_id", System.Data.SqlDbType.Int)).Value = user_id;
        sCmd.Parameters.Add(new SqlParameter("@clientt", System.Data.SqlDbType.NVarChar)).Value = "false";
        conn.Close();
        conn.Open();
        SqlDataReader dr = sCmd.ExecuteReader();
        while (dr.Read())
        {
            list.Add(Fill_User_company(dr));
        }
        dr.Close();
        conn.Close();
        return list;
    }

    //Atlasam nepieciešamo priekš MyClients.aspx saraksta
    public static List<_User_company> Select_User_Clients(string user_id)
    {
        List<_User_company> list = new List<_User_company>();
        SqlCommand sCmd = new SqlCommand("SELECT * FROM user_company WHERE id_user=@user_id AND client=@clientt", conn);
        sCmd.Parameters.Add(new SqlParameter("@user_id", System.Data.SqlDbType.Int)).Value = user_id;
        sCmd.Parameters.Add(new SqlParameter("@clientt", System.Data.SqlDbType.NVarChar)).Value = "true";
        conn.Close();
        conn.Open();
        SqlDataReader dr = sCmd.ExecuteReader();
        while (dr.Read())
        {
            list.Add(Fill_User_company(dr));
        }
        dr.Close();
        conn.Close();
        return list;
    }

    //Atlasa nepieciešamo priekš ADD/EDIT POPUP
    public static _User_company Sel_User_company(string id_company)
    {
        _User_company user_company = new _User_company();
        SqlCommand sCmd = new SqlCommand("SELECT * FROM user_company WHERE id_company=@company_id", conn);
        sCmd.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = id_company;
        conn.Open();
        SqlDataReader dr = sCmd.ExecuteReader();
        while (dr.Read())
        {
            user_company._id_user_company = (int)dr[0];

            user_company._id_company = (int)dr[1];
            user_company._company_name = _Company.SelectCompany(dr[1].ToString(), "")._company_name;
            user_company._company_reg_nr = _Company.SelectCompany(dr[1].ToString(), "")._company_reg_nr;
            user_company._company_vat = _Company.SelectCompany(dr[1].ToString(), "")._company_vat;
            user_company._status = _Company.SelectCompany(dr[1].ToString(), "")._status;

            user_company._id_user = (int)dr[2];
            user_company._main_company = (bool)dr[3];
            user_company._position = dr[4].ToString();
            user_company._client = dr[5].ToString();
        }
        dr.Close();
        conn.Close();
        return user_company;
    }

    //Kompānijas/Klienta pievienošana AddCompany.aspx
    public static _User_company Insert_Company(string company_name, string company_reg_nr, string company_vat, string id_user, string main_cmpany, string position, string client)
    {
        _User_company user_company = new _User_company();
        SqlCommand AddCmd_company = new SqlCommand("INSERT INTO company (company_name, company_reg_nr, company_vat, statuss) " + "  VALUES (@company_name, @company_reg_nr, @company_vat, @status);", conn);
        AddCmd_company.Parameters.Add(new SqlParameter("@company_name", System.Data.SqlDbType.NVarChar)).Value = company_name;
        AddCmd_company.Parameters.Add(new SqlParameter("@company_reg_nr", System.Data.SqlDbType.BigInt)).Value = Convert.ToInt64(company_reg_nr);
        AddCmd_company.Parameters.Add(new SqlParameter("@company_vat", System.Data.SqlDbType.NVarChar)).Value = company_vat;
        AddCmd_company.Parameters.Add(new SqlParameter("@status", System.Data.SqlDbType.NVarChar)).Value = "Active";
        conn.Close();
        conn.Open();
        AddCmd_company.ExecuteNonQuery();
        conn.Close();

        conn.Open();
        SqlCommand SelCmd_MAXcompany = new SqlCommand("SELECT MAX(id) FROM company", conn);
        string id_company = Convert.ToString(SelCmd_MAXcompany.ExecuteScalar());
        conn.Close();

        SqlCommand AddCmd_user_company = new SqlCommand("INSERT INTO user_company (id_company, id_user, main_company, position, client) " + "  VALUES (@id_company, @id_user, @main_company, @position, @client);", conn);
        AddCmd_user_company.Parameters.Add(new SqlParameter("@id_company", System.Data.SqlDbType.Int)).Value = id_company;
        AddCmd_user_company.Parameters.Add(new SqlParameter("@id_user", System.Data.SqlDbType.Int)).Value = id_user;
        AddCmd_user_company.Parameters.Add(new SqlParameter("@main_company", System.Data.SqlDbType.NVarChar)).Value = main_cmpany;
        AddCmd_user_company.Parameters.Add(new SqlParameter("@position", System.Data.SqlDbType.NVarChar)).Value = position;
        AddCmd_user_company.Parameters.Add(new SqlParameter("@client", System.Data.SqlDbType.NVarChar)).Value = client;
        conn.Open();
        AddCmd_user_company.ExecuteNonQuery();
        conn.Close();

        return user_company;
    }

    //Kompānijas/Klienta labošana EditCompany.aspx
    public static _User_company Update(string id_user_company, string id_user, string id_company, string main_company, string position, string name, string reg_nr, string vat, string client, string status)
    {
        _User_company user_company = new _User_company();
        SqlCommand UpCmd_company = new SqlCommand("UPDATE company SET company_name=@company_name, company_reg_nr=@company_reg_nr, company_vat=@company_vat, statuss=@status WHERE ID=@ID", conn);
        UpCmd_company.Parameters.Add(new SqlParameter("@ID", System.Data.SqlDbType.Int)).Value = id_company;
        UpCmd_company.Parameters.Add(new SqlParameter("@company_name", System.Data.SqlDbType.NVarChar)).Value = name;
        UpCmd_company.Parameters.Add(new SqlParameter("@company_reg_nr", System.Data.SqlDbType.BigInt)).Value = Convert.ToInt64(reg_nr);
        UpCmd_company.Parameters.Add(new SqlParameter("@company_vat", System.Data.SqlDbType.NVarChar)).Value = vat;
        UpCmd_company.Parameters.Add(new SqlParameter("@status", System.Data.SqlDbType.NVarChar)).Value = status;
        conn.Open();
        UpCmd_company.ExecuteNonQuery();
        conn.Close();

        SqlCommand UpCmd_user_company = new SqlCommand("UPDATE user_company SET id_company=@id_company, id_user=@id_user, main_company=@main_company, position=@position, client=@client WHERE ID=@ID", conn);
        UpCmd_user_company.Parameters.Add(new SqlParameter("@ID", System.Data.SqlDbType.Int)).Value = id_user_company;
        UpCmd_user_company.Parameters.Add(new SqlParameter("@id_user", System.Data.SqlDbType.Int)).Value = id_user;
        UpCmd_user_company.Parameters.Add(new SqlParameter("@id_company", System.Data.SqlDbType.Int)).Value = id_company;
        UpCmd_user_company.Parameters.Add(new SqlParameter("@main_company", System.Data.SqlDbType.NVarChar)).Value = main_company;
        UpCmd_user_company.Parameters.Add(new SqlParameter("@position", System.Data.SqlDbType.NVarChar)).Value= position;
        UpCmd_user_company.Parameters.Add(new SqlParameter("@client", System.Data.SqlDbType.NVarChar)).Value = client;
        conn.Open();
        UpCmd_user_company.ExecuteNonQuery();
        conn.Close();

        return user_company;
    }

    //Kompānijas/Klienta dzēšana MyCompanies.aspx
    public static _User_company Delete(string id_company)
    {
        _User_company user_company = new _User_company();

        SqlCommand UpCmd_company = new SqlCommand("UPDATE company SET statuss=@status WHERE ID=@ID", conn);
        UpCmd_company.Parameters.Add(new SqlParameter("@ID", System.Data.SqlDbType.Int)).Value = id_company;
        UpCmd_company.Parameters.Add(new SqlParameter("@status", System.Data.SqlDbType.NVarChar)).Value = "Deleted";
        conn.Open();
        UpCmd_company.ExecuteNonQuery();
        conn.Close();

        return user_company;
    }

    //Atlasa nepieciešamo priekš default.aspx popup
    public static _User_company Select_company(string user_id)
    {
        _User_company user_company = new _User_company();
        SqlCommand sCmd = new SqlCommand("SELECT * FROM user_company WHERE id_user=@company_id", conn);
        sCmd.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = user_id;
        conn.Open();
        SqlDataReader dr = sCmd.ExecuteReader();
        while (dr.Read())
        {
            user_company._id_user_company = (int)dr[0];

            user_company._id_company = (int)dr[1];
            user_company._company_name = _Company.SelectCompany(dr[1].ToString(), "")._company_name;
            user_company._company_reg_nr = _Company.SelectCompany(dr[1].ToString(), "")._company_reg_nr;
            user_company._company_vat = _Company.SelectCompany(dr[1].ToString(), "")._company_vat;
            user_company._status = _Company.SelectCompany(dr[1].ToString(), "")._status;

            user_company._id_user = (int)dr[2];
            user_company._main_company = (bool)dr[3];
            user_company._position = dr[4].ToString();
            user_company._client = dr[5].ToString();
        }
        dr.Close();
        conn.Close();
        return user_company;
    }
}